【Alteryx】ファイル名もシート名も異なるExcelファイルのデータを一気に読み込めるマクロに、列チェック機能を入れてみた
こんにちは、まつおかです。
以前「ファイル名もシート名も異なるExcelファイルのデータを一気に読み込めるマクロを作ってみた」というブログを書いたのですが・・・
今日はそのマクロをちょっと（いや、けっこう？）変更して、入力データの列が意図したものとなっているかをチェックする機能を追加してみました。
以前作成したマクロではデータの読込みに動的入力ツールを使用していたため、少しでも列名が異なるとエラーになり処理は強制終了、列が多い場合は警告が出て該当のシートは読み込まれないまま処理が終了してしまいます。
いつも同じシステムから出力したファイルなど、必ず同じフォーマットのファイルを読み込むのであれば動的入力ツールで問題はありませんが、複数の人が編集するファイルなど、意図しない列となってしまう可能性のある場合は列のチェック機能を入れて、どのファイルのどのシートのどの項目が正しくないのかわかるようにしておくことで、修正対象がすぐにわかり、ファイルの修正に手間取るという問題を防ぐことができます。
環境と使用データ
今回のマクロ作成に使用した環境は以下のとおりです。
- Windows 10 Pro
- Alteryx Designer 2021.4.2
データはTableau Desktopをインストールするとデフォルトで付いているサンプルデータ「スーパーストア」を使用し、オーダー年月ごとにシートを分け、かつ3ヶ月分を1ファイルにまとめたExcelファイルをいくつか用意しました。
作成したワークフローはこちら
全体のワークフローは以下のようになります。
まずは読み込み対象のファイル情報を取得し、バッチマクロ「Data Input」に渡します。次に「Data Input」内で対象のファイル、シートのデータを読み込んだ後、列名のチェックを実施します。
マクロ呼び出しワークフロー
まずはデータ入力ツールにて、データが格納されているディレクトリを指定し、ファイルとシートのリストを取得します。
次にフォーミュラツールにて、データ入力ツールで取得したファイルパスとシートのリストからマクロ内のデータ入力ツールに渡すために「＜ファイルパス＞|||＜シート名＞」という形式の文字列を作成します。
データ入力ツールからは以下のような状態でデータが出力されますので、
「FileName」の「<List of Sheet Names>」を「Sheet Names」で置換する式を設定します。
今回は読み込む列の範囲まで指定しています。（最後の「＄A:V」がそれに該当）
Replace([FileName],"<List of Sheet Names>",[Sheet Names])+"$A:V"
バッチマクロ（Data Input）
マクロ内では1シートずつデータを読み込んだ上で、データが意図した列となっているかをチェックし、不足している列がある場合はエラーメッセージとしてファイル名、シート名、不足している列名を表示します。
①データ入力ツール、②アクションツール
対象のファイル、シートからデータを読み込みます。ファイルパス＆シートの情報は呼び出し元から渡されたデータを使いアクションツール内で更新します。
③基本データプロファイルツール、④集計ツール
読み込んだデータの列情報を取得するには「基本データプロファイルツール」を使用します。
基本データプロファイルツールから出力されたデータは以下のようになっており、列の各種情報が行として出力されます。読み込んだデータの列名は「FieldName」列に入っていますので、集計ツールを使用し複数行ある列名をユニークにします。
⑤テキスト入力ツール、⑥結合ツール
テキスト入力ツールには、あらかじめ正しい列名のリストを用意しておき、結合ツールを使って④で出力された列名と結合することで、不足している列がないかを確認します。
⑤テキスト入力ツールのデータ
⑥結合ツールのLアンカーから、不足している列名（テキスト入力にしかなかった行）が出力されます。
⑦集計ツール
次に集計ツールを使用し、不足している列名をカンマ区切りで連結（列名が複数ある場合）します。
カンマ区切りで連結すると以下のようになります。
⑧、⑨メッセージツール
エラーメッセージを表示します。
メッセージを送信するタイミングを「最初のレコードの前」とすることで、不足している列がある場合のみメッセージが表示されるようになります。
⑧のメッセージツールでは、エラー対象のファイル名を表示します。マクロ呼び出し元から渡されたパラメータをメッセージツールのQアンカーに接続することで、[#1]となっているところにファイルとシートの情報が入ります。
⑨のメッセージツールでは、不足している列名を表示します。⑦の集計ツールから出力された不足している列名をメッセージ式に設定します。
実際に出力されるメッセージは以下のようになります。
不足している列がない場合はメッセージツールを通るデータもありませんので、もちろんエラーも発生しませんし、メッセージも表示されません。
⑩フィールド付加ツール
最後のフィールド不可ツールですが、これがなぜ必要なのか・・・？と思われた方もいらっしゃるかもしれません。これは、バッチマクロが列構成の異なるデータを検知したことで発生させるエラーを遅らせるためのものです。
フィールド付加ツールを入れなかった場合のマクロはこのようになります。
この状態でもマクロとして問題はないので処理自体は成立するのですが、①は何の処理もしないまま出力するだけなので、②と比べ明らかに先に処理が終わります。この場合、もしも入力ツールで読み込んだデータの列構成が意図しないものとなっていたとすると、②のルート中にあるメッセージツールでエラーメッセージが表示されるよりも前にマクロ自体がエラーメッセージを出力してしまい処理がストップしてしまいます。マクロのエラーメッセージから列構成に問題があるということはわかったとしても、どのファイルのどのシートに、何の列が足りていないのかまではわからない状態でストップしてしまうことになります。
この問題を回避するためにフィールド付加ツールを挟んでいます。フィールドを付加するということは、②の処理を通らないと付加できませんので（たとえ付加するデータがなかったとしても）、必ずメッセージツールを通過してからマクロのアウトプットを通過することになります。
そんなわけで、このフィールド付加ツールはとってもいい仕事をしてくれる必要なツールなのです！
以上が処理の一連の流れとなります。
さいごに
「大量にあるExcelファイルとシート、動的入力ツールでまとめて読み込めると思ってたのにエラーになってしまい。。。どれが悪いのかわからへんしどうすりゃええねーん！」っていうこと、あるあるではないでしょうか。「対象のファイルはデータシートだけだと思っていたのにいつの間にかメモ用のシートも追加されてるし・・・」なんてことも。
そんなときはこのチェック機能を追加してみてください。
構築時にはちょっとだけ手間が必要にはなりますが、どのシートの列が意図しないものになっているのか、どのシートは読み込み対象から外すべきなのか（この場合はフィルターツールを使って対象外のシートを除く処理を追加してくださいね）、ストレスなく発見することができます！
ということで、ぜひお試しを。
